OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061) by Roopesh Ramklass

Author:Roopesh Ramklass
Language: eng
Format: epub
Tags: -
Publisher: McGraw-Hill Education
Published: 2013-03-13T16:00:00+00:00

There are three functions in the SELECT list which, from inner to outer levels, are TO_DATE, TO_CHAR and LENGTH. The query is evaluated as follows:

1. The innermost function is evaluated first. TO _ DATE('28/10/09','DD/MM/RR' converts the character string 28/10/09 into the DATE value 28-OCT-2009. The RR format mask is used for the year portion. Therefore, the century component returned is the current century (the twenty-first), since the year component is between 0 and 49.

2. The second innermost function is evaluated next. TO _ CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

3. Finally, the LENGTH('October') function is evaluated and the query returns the number 7.

SCENARIO & SOLUTION

Are nested functions evaluated from the outermost level to the innermost level? No. Nested functions are resolved from the innermost nested level moving outward.

Must all functions in a nested expression return the same data type? No. The data types of the parameters of nested functions may be different from each other. It is important to ensure that the correct data types are always supplied to functions to avoid errors.

Is there a simpler way to display the SALARY information from the EMPLOYEES table in the form $13,000 without using the following statement? SELECT '$'|| SUBSTR(SALARY,1, MOD(LENGTH(SALARY),3))||', '|| SUBSTR(SALARY, MOD (LENGTH(SALARY),3)+1)

Yes. A simple and elegant solution is to use the TO_CHAR function with the '$99G999' format mask: SELECT TO_CHAR(SALARY, '$99G999') FROM EMPLOYEES;

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.